using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data.OleDb;
using XProject.Entity;
namespace XProject.DAO
{
    class SayingDAO:DBAccess
    {
        #region sql command
        string SQL_SELECT_ID = "select max(SayingID) from Saying";
        string SQL_SELECT_BY_ID = "select * from Saying where SayingID=@sayingId";
        string SQL_SELECT_TOP="select top 20 SayingID, TypeID, Content, Owner,[Note] from Saying";
        string SQL_SELECT_CONTENT="select * from Saying where Content like @keyword or Owner like @keyword";
        string SQL_SELECT_CONTENT_AND_TYPE = "select * from Saying where TypeID=@typeId and Content like @keyword";

        string SQL_INSERT = "insert into Saying([Content],[TypeID],[Owner],[Note]) values(@content,@typeId,@owner,@note)";
        string SQL_UPDATE = "update Saying set Content=@content,TypeID=@typeId, [Owner]=@owner, [Note]=@note where SayingID=@sayingId";
        string SQL_DELETE = "delete from Saying where SayingID=@sayingId";
        string SQL_SELECT_ALL_ID = "select SayingID from Saying";
        #endregion
        #region fields
        string message;
        #endregion
        #region constructors
        public SayingDAO() { message = ""; }
        #endregion
        #region properties
        public string Message
        {
            get { return message; }
            set { message = value; }
        }
        #endregion
        #region methods
        public Saying GetByID(int id)
        {
            Saying saying = new Saying();
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_SELECT_BY_ID, conn);
                command.Parameters.Add("@sayingId", OleDbType.Integer).Value = id;
                if (Connect())
                {
                    OleDbDataReader dr = command.ExecuteReader();
                    if (dr.Read())
                    {                       
                        saying.SayingID = dr.GetInt32(0);
                        saying.Type.TypeID = dr.GetInt32(1);
                        saying.Content = dr.GetString(2);
                        saying.Owner = dr.GetString(3);
                        saying.Note = dr.GetString(4);                
                    }
                    Disconnect();
                }
            }
            catch (OleDbException ex) { message = ex.Message; }
            catch (Exception ex) { message = ex.Message; }
            return saying;
        }
        public IList<Saying> GetTop()
        {
            IList<Saying> list = new List<Saying>();
            try
            {
                OleDbCommand  command = new OleDbCommand(SQL_SELECT_TOP, conn);
                if (Connect())
                {
                    OleDbDataReader dr = command.ExecuteReader();
                    while (dr.Read())
                    {
                        Saying saying = new Saying();
                        saying.SayingID = dr.GetInt32(0);
                        saying.Type.TypeID=dr.GetInt32(1);
                        saying.Content = dr.GetString(2);
                        saying.Owner = dr.GetString(3);
                        saying.Note = dr.GetString(4);
                        list.Add(saying);
                    }
                    Disconnect();
                }
            }
            catch (OleDbException ex) { message = ex.Message; }
            catch(Exception ex){message=ex.Message;}
            return list;
        }
        public IList<Saying> GetByContent(string keyword)
        {
            IList<Saying> list = new List<Saying>();
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_SELECT_CONTENT, conn);
                command.Parameters.Add("@keyword", OleDbType.VarChar).Value = keyword;
                if (Connect())
                {
                    OleDbDataReader dr = command.ExecuteReader();
                    while (dr.Read())
                    {
                        Saying saying = new Saying();
                        saying.SayingID = dr.GetInt32(0);
                        saying.Type.TypeID = dr.GetInt32(1);
                        saying.Content = dr.GetString(2);
                        saying.Owner = dr.GetString(3);
                        saying.Note = dr.GetString(4);
                        list.Add(saying);
                    }
                    Disconnect();
                }
            }
            catch (OleDbException ex) { message = ex.Message; }
            catch (Exception ex) { message = ex.Message; }
            return list;
        }
        /// <summary>
        /// Search item by type and keyword
        /// </summary>
        /// <param name="keyword"></param>
        /// <returns></returns>
        public IList<Saying> GetByTypeAndContent(int typeID,string keyword)
        {
            IList<Saying> list = new List<Saying>();
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_SELECT_CONTENT_AND_TYPE, conn);
                command.Parameters.Add("@typeId", OleDbType.Integer).Value = typeID;
                command.Parameters.Add("@keyword", OleDbType.VarChar).Value = keyword;
                if (Connect())
                {
                    OleDbDataReader dr = command.ExecuteReader();
                    while (dr.Read())
                    {
                        Saying saying = new Saying();
                        saying.SayingID = dr.GetInt32(0);
                        saying.Type.TypeID = dr.GetInt32(1);
                        saying.Content = dr.GetString(2);
                        saying.Owner = dr.GetString(3);
                        saying.Note = dr.GetString(4);
                        list.Add(saying);
                    }
                    Disconnect();
                }
            }
            catch (OleDbException ex) { message = ex.Message; }
            catch (Exception ex) { message = ex.Message; }
            return list;
        }
        public ArrayList GetAllKey()
        {
            ArrayList list = new ArrayList();
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_SELECT_ALL_ID, conn);
                if (Connect())
                {
                    OleDbDataReader dr = command.ExecuteReader();
                    while (dr.Read())
                    {
                        list.Add(dr.GetInt32(0));
                    }
                    Disconnect();
                }
            }
            catch (OleDbException ex) { message = ex.Message; }
            catch (Exception ex) { message = ex.Message; }
            return list;
        }
        public int Insert(Saying saying)
        {
            int effectedRows = 0;
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_INSERT, conn);
                command.Parameters.Add("@content", OleDbType.VarChar).Value = saying.Content;
                command.Parameters.Add("@typeId", OleDbType.Integer).Value = saying.Type.TypeID;               
                command.Parameters.Add("@owner", OleDbType.VarChar).Value = saying.Owner;
                command.Parameters.Add("@note", OleDbType.VarChar).Value = saying.Note;
                if (Connect())
                {
                    effectedRows = command.ExecuteNonQuery();
                    command.CommandText = SQL_SELECT_ID;
                    saying.SayingID = int.Parse(command.ExecuteScalar().ToString());
                    Disconnect();
                }
            }
            catch (OleDbException ex) { message = ex.Message; }
            catch (Exception ex) { message = ex.Message; }
            return effectedRows;
        }
        public int Update(Saying saying)
        {
            int effectedRows = 0;
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_INSERT, conn);
                command.Parameters.Add("@content", OleDbType.VarChar).Value = saying.Content;
                command.Parameters.Add("@typeId", OleDbType.Integer).Value = saying.Type.TypeID;                
                command.Parameters.Add("@owner", OleDbType.VarChar).Value = saying.Owner;
                command.Parameters.Add("@note", OleDbType.VarChar).Value = saying.Note;
                command.Parameters.Add("@sayingId",OleDbType.Integer).Value=saying.SayingID;
                if (Connect())
                {
                    effectedRows = command.ExecuteNonQuery();
                    command.CommandText = SQL_SELECT_ID;
                    saying.SayingID = int.Parse(command.ExecuteScalar().ToString());
                    Disconnect();
                }
            }
            catch (OleDbException ex) { message = ex.Message; }
            catch (Exception ex) { message = ex.Message; }
            return effectedRows;
        }
        public int Delete(Saying saying)
        {
            int effectedRows = 0;
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_DELETE, conn);
                command.Parameters.Add("@sayingId", OleDbType.Integer).Value = saying.SayingID;
                if (Connect())
                {
                    effectedRows = command.ExecuteNonQuery();                   
                    Disconnect();
                }
            }
            catch (OleDbException ex) { message = ex.Message; }
            catch (Exception ex) { message = ex.Message; }
            return effectedRows;
        }
        #endregion
    }
}
